﻿using System;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using eCashDataLayer;

namespace eCashDatalayer
{
    public class eCashDatalayer : IeCashDatalayer
    {
        String strCon = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;

        SqlCommand cmdselect;

        SqlConnection con;

        SqlDataAdapter da;


        public void RegisterUser(RegistrationModel RegistrationModel)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_Registeruser";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;

            cmdselect.Parameters.Add("@FIRSTNAME", SqlDbType.VarChar).Value = RegistrationModel.sFirstName;

            cmdselect.Parameters.Add("@LASTNAME", SqlDbType.VarChar).Value = RegistrationModel.sLastname;

            cmdselect.Parameters.Add("@Username", SqlDbType.VarChar).Value = RegistrationModel.sUsername;

            cmdselect.Parameters.Add("@Password", SqlDbType.VarChar).Value = RegistrationModel.sPassword;

            cmdselect.Parameters.Add("@GUIDR", SqlDbType.VarChar).Value = RegistrationModel.sGUIDR;

            cmdselect.Parameters.Add("@Email", SqlDbType.VarChar).Value = RegistrationModel.sEmail;

            cmdselect.Parameters.Add("@Tel", SqlDbType.VarChar).Value = RegistrationModel.sTel;

            cmdselect.Parameters.Add("@Province", SqlDbType.VarChar).Value = RegistrationModel.sProvince;

            try
            {
                con.Open();

                cmdselect.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }

        }




        public void RegisterUserTuckShop(TuckShopRegistrationModel TuckshopRegistrationModel)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_RegisteruserTuckshop";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;

            cmdselect.Parameters.Add("@TUCKSHOPNAME", SqlDbType.VarChar).Value = TuckshopRegistrationModel.sTuckShopname;

            cmdselect.Parameters.Add("@SCHOOLNAME", SqlDbType.VarChar).Value = TuckshopRegistrationModel.sSchoolname;

            cmdselect.Parameters.Add("@Username", SqlDbType.VarChar).Value = TuckshopRegistrationModel.sUsername;

            cmdselect.Parameters.Add("@Password", SqlDbType.VarChar).Value = TuckshopRegistrationModel.sPassword;
             
            cmdselect.Parameters.Add("@GUIDR", SqlDbType.VarChar).Value = TuckshopRegistrationModel.sGUIDR;

            cmdselect.Parameters.Add("@Email", SqlDbType.VarChar).Value = TuckshopRegistrationModel.sEmail;

            cmdselect.Parameters.Add("@Tel", SqlDbType.VarChar).Value = TuckshopRegistrationModel.sTel;

            cmdselect.Parameters.Add("@Province", SqlDbType.VarChar).Value = TuckshopRegistrationModel.sProvince;

            try
            {
                con.Open();

                cmdselect.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }

        }

        public int CheckUserExistance(String _sUSERNAME)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_CheckUserExistance";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;

            cmdselect.Parameters.Add("@USERNAME", SqlDbType.VarChar).Value = _sUSERNAME;

            cmdselect.Parameters.Add("@RESULTS", SqlDbType.Int);

            cmdselect.Parameters["@RESULTS"].Direction = ParameterDirection.Output;

            int isValid = 0;

            try
            {
                con.Open();

                cmdselect.ExecuteNonQuery();

                isValid = (int)cmdselect.Parameters["@RESULTS"].Value;

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return isValid;
        }

        public int CheckEmailExistance(String _sEMAIL)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_CheckemailExistance";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;

            cmdselect.Parameters.Add("@EMAIL", SqlDbType.VarChar).Value = _sEMAIL;

            cmdselect.Parameters.Add("@RESULTS", SqlDbType.Int);

            cmdselect.Parameters["@RESULTS"].Direction = ParameterDirection.Output;

            int isValid = 0;

            try
            {
                con.Open();

                cmdselect.ExecuteNonQuery();

                isValid = (int)cmdselect.Parameters["@RESULTS"].Value;

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return isValid;
        }
        
            
        public  RegistrationModel RecoverCredentials (RegistrationModel RecoverCredentials)
        {
        
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_RecoveruserCredentials";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            if (RecoverCredentials.sEmail != null)
            {
                cmdselect.Parameters.Add("@EMAIL", SqlDbType.VarChar).Value = RecoverCredentials.sEmail;
            }

            if (RecoverCredentials.sUsername != null)
            {
                cmdselect.Parameters.Add("@USERNAME", SqlDbType.VarChar).Value =  RecoverCredentials.sUsername;
            }

            RegistrationModel m = new RegistrationModel();

            DataTable dtcredentials = new DataTable();
           
            da.SelectCommand  = cmdselect;
            try
            {
                con.Open();

                da.Fill(dtcredentials);

                 
                if (dtcredentials.Rows.Count > 0)
                {

                    for (int i = 0; i < dtcredentials.Rows.Count; i++)
                    {
                        m.sUsername = dtcredentials.Rows[i][0].ToString();
                        m.sPassword =  dtcredentials.Rows[i][1].ToString();
                        m.sEmail = dtcredentials.Rows[i][2].ToString(); 
                    }
                }
                
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return m;
        }


        private UserModel GetPermissions(String Username)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_GetPagePermission";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            cmdselect.Parameters.Add("@USERNAME", SqlDbType.VarChar).Value = Username;

            UserModel m = new UserModel();

            DataTable dtpermissions = new DataTable();
           
            da.SelectCommand  = cmdselect;

            List<string> LstPermissions = new List<string>();

            try
            {
                con.Open();

                da.Fill(dtpermissions);


                if (dtpermissions.Rows.Count > 0)
                {

                    for (int i = 0; i < dtpermissions.Rows.Count; i++)
                    {
                        m.iUserid = Convert.ToInt32(dtpermissions.Rows[i][0]);
                        m.sUsername = dtpermissions.Rows[i][1].ToString();
                        m.sFirstName = dtpermissions.Rows[i][2].ToString();
                        m.sLastname = dtpermissions.Rows[i][3].ToString();
                        m.sEmailAddress = dtpermissions.Rows[i][4].ToString();
                        m.bIsactive = Convert.ToBoolean(dtpermissions.Rows[i][6]);
                        m.bIsconfirmed = Convert.ToBoolean(dtpermissions.Rows[i][7]);
                    }
                    foreach (DataRow row in dtpermissions.Rows)
                    {
                        //get List of Permissions 
                        LstPermissions.Add(row["PERMISSIONNAME"].ToString());
                    }
                }
                m.bIsValid = true; //this will confirm to the BLL Function that this login is Valid 
                m.sPermissions = LstPermissions;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return m;


        }

        public int ConfirmRegistration(String _sGUID)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_ConfirmRegistration";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;

            cmdselect.Parameters.Add("@GUID", SqlDbType.VarChar).Value = _sGUID;

            cmdselect.Parameters.Add("@Valid", SqlDbType.Int);

            cmdselect.Parameters["@Valid"].Direction = ParameterDirection.Output;

            int isValid = 0;

            //if(

            try
            {
                con.Open();

                cmdselect.ExecuteNonQuery();

                isValid = (int)cmdselect.Parameters["@Valid"].Value;

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return isValid;

        }


        public UserModel AuthonticateUser(String _sUsername, String _sPassword)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_Authonticateuser";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;

            cmdselect.Parameters.Add("@USERNAME", SqlDbType.VarChar).Value = _sUsername;

            cmdselect.Parameters.Add("@PASSWORD", SqlDbType.VarChar).Value = _sPassword;

            cmdselect.Parameters.Add("@RESULTS", SqlDbType.Int);

            cmdselect.Parameters["@RESULTS"].Direction = ParameterDirection.Output;

            int isValid = 0;

            UserModel m = new UserModel();

            try
            {
                con.Open();

                cmdselect.ExecuteNonQuery();

                isValid = (int)cmdselect.Parameters["@RESULTS"].Value;


                if(isValid > 0)
                {

                   m = GetPermissions(_sUsername);
                }
                else
                {
                    m.bIsValid = false;

                }

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return m;

        }



        
    }
}